package edu.zut.cs.network.merhandise.goods.dao.impl;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Properties;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import edu.zut.cs.network.merhandise.goods.dao.MerhandiseDao;
import edu.zut.cs.network.merhandise.goods.entity.Merhandise;

public class MerhandiseDaoImpl implements MerhandiseDao {
	protected Logger logger = LogManager.getLogger(this.getClass().getName());
	Connection conn;
	PreparedStatement st;
	ResultSet rs;
	private String driver;
	private String url;
	private String user;
	private String pass;
	List<Merhandise> merhandise = null;

	public MerhandiseDaoImpl() {
		try {
			this.initParam("mysql.ini");
		} catch (FileNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} catch (IOException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try {
			try {
				Class.forName(driver);
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			conn = DriverManager.getConnection(url, user, pass);
			st = conn.prepareStatement("INSERT INTO T_MERHANDISE VALUE(NULL,?,?,?)");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void initParam(String paramfile) throws FileNotFoundException, IOException {
		Properties props = new Properties();
		props.load(new FileInputStream(paramfile));
		driver = props.getProperty("driver");
		url = props.getProperty("url");
		user = props.getProperty("user");
		pass = props.getProperty("pass");
	}

	private int useSql(String sql, Merhandise merhandise) {
		int result = 0;
		try {
			result = st.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (st != null) {
					st.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result;
	}

	public void create(Merhandise merhandise) {
		// TODO Auto-generated method stub
		String sql = "INSERT INTO T_MERHANDISE( ID , NAME , PRICE, SALES ) VALUES(" + null + ",'" + merhandise.getName()
				+ "','" + merhandise.getPrice() + "','" + merhandise.getSales() + "')";
		
		logger.debug(merhandise);
		logger.debug('\n');
		logger.debug("  添加信息成功！");
		useSql(sql, merhandise);

	}

	public int remove(Merhandise merhandise) {
		// TODO Auto-generated method stub
		String sql = "DELETE FROM T_MERHANDISE WHERE ID = " + merhandise.getId();
		
		logger.debug(merhandise);
		logger.debug('\n');
		logger.debug("  刪除信息成功！");
		return useSql(sql, merhandise);
	}

	public void update(Merhandise merhandise) throws SQLException {
		// TODO Auto-generated method stub
		String sql = "UPDATE T_MERHANDISE SET NAME = '" + merhandise.getName() + "' , PRICE = '" + merhandise.getPrice()
				+ "' , SALES = '" + merhandise.getSales() + "' WHERE ID = " + merhandise.getId();
		useSql(sql, merhandise);
		logger.debug(merhandise);
		logger.debug("\n");
		logger.debug(" 更新信息成功！");
	}

	public Merhandise findByName(String name) {
		// TODO Auto-generated method stub
		Merhandise merhandise = null;
		try {
			String selectSQL = "SELECT ID, NAME, PRICE, SALES FROM T_MERHANDISE WHERE NAME =  " + name;
			/*logger.debug(merhandise);
			logger.debug('\n');*/
			ResultSet rs = this.st.executeQuery(selectSQL);
			while (rs.next()) {
				merhandise = new Merhandise();
				merhandise.setId(rs.getLong("ID"));
				merhandise.setName(rs.getString("NAME"));
				merhandise.setPrice(rs.getString("PRICE"));
				merhandise.setSales(rs.getString("SALES"));
				if (merhandise.getName() == name) {
					logger.info("found merhandise:" + merhandise);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (st != null) {
					st.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return merhandise;
	}

	public Merhandise findById(Long id) {
		// TODO Auto-generated method stub
		Merhandise merhandise = null;
		try {
			String selectSQL = "SELECT ID , NAME , PRICE , SALES FROM T_MERHANDISE WHERE ID = " + id;
			//logger.debug(merhandise);
			//logger.debug('\n');
			ResultSet rs = this.st.executeQuery(selectSQL);
			while (rs.next()) {
				merhandise = new Merhandise();
				merhandise.setId(rs.getLong("ID"));
				merhandise.setName(rs.getString("NAME"));
				merhandise.setPrice(rs.getString("PRICE"));
				merhandise.setSales(rs.getString("SALES"));
				if (merhandise.getId()==id) {
					logger.info("found merhandise:" + merhandise);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (st != null) {
					st.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return merhandise;
	}

}
